﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

namespace Liwey.Common
{
    /// <summary>
    /// 通用数据库访问类，封装了对数据库的常见操作
    ///</summary>
    public sealed class DbHelper
    {
        public string ConnectionString { get; set; }
        private DbProviderFactory providerFactory;
        private DbProviderType providerType;

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="providerType">数据库类型枚举，参见<paramref name="providerType"/></param>
        public DbHelper(DbProviderType providerType = DbProviderType.SqlServer)
        {
            this.providerType = providerType;
            providerFactory = ProviderFactory.GetDbProviderFactory(providerType);
            if (providerFactory == null)
            {
                throw new ArgumentException("Can't load DbProviderFactory for given value of providerType.");
            }
        }


        public DbHelper(string host, string db, string username, string password, DbProviderType providerType = DbProviderType.SqlServer)
        {
            this.providerType = providerType;
            providerFactory = ProviderFactory.GetDbProviderFactory(providerType);
            if (providerFactory == null)
            {
                throw new ArgumentException("Can't load DbProviderFactory for given value of providerType.");
            }

            switch (providerType)
            {
                case DbProviderType.SqlServer:
                    ConnectionString = string.Format(@"Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3};",
                    host, db, username, password);
                    break;

                case DbProviderType.MySql:
                    ConnectionString = string.Format(@"Server={0};Database={1};Uid={2};Pwd={3};",
                    host, db, username, password);
                    break;

                case DbProviderType.Oracle:
                    ConnectionString = string.Format(@"server={0};data source={1};user id={2};password={3}",
                    host, db, username, password);
                    break;

                default:
                    throw new Exception("This method is not supported for providerType " + providerType.ToString());
            }
        }

        public DbHelper(string connectionString, DbProviderType providerType = DbProviderType.SqlServer)
        {
            ConnectionString = connectionString;
            this.providerType = providerType;
            providerFactory = ProviderFactory.GetDbProviderFactory(providerType);
            if (providerFactory == null)
            {
                throw new ArgumentException("Can't load DbProviderFactory for providerType " + providerType);
            }
        }

        /// <summary>   
        /// 对数据库执行增删改操作，返回受影响的行数。   
        /// </summary>   
        /// <param name="sql">要执行的增删改的SQL语句</param>   
        /// <param name="parameters">执行增删改语句所需要的参数</param>
        /// <param name="commandType">执行的SQL语句的类型</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql, params object[] parameters)
        {
            using (DbCommand command = CreateDbCommand(FormatSql(sql, parameters)))
            {
                command.Connection.Open();
                int affectedRows = command.ExecuteNonQuery();
                command.Connection.Close();
                return affectedRows;
            }
        }

        public DbParameter CreateParameter(string name, object value)
        {
            DbParameter p = this.providerFactory.CreateParameter();
            p.ParameterName = name;
            p.Value = value;
            return p;
        }

        public static bool ExecuteNoQueryTran(List<String> sqls)
        {
            return false;
        }

        public int ExecuteNonQueryWithParams(string sql, params DbParameter[] parameters)
        {
            using (DbCommand command = CreateDbCommand(sql, parameters))
            {
                command.Connection.Open();
                int affectedRows = command.ExecuteNonQuery();
                command.Connection.Close();
                return affectedRows;
            }
        }

        /// <summary>   
        /// 执行一个查询语句，返回一个关联的DataReader实例   
        /// </summary>   
        /// <param name="sql">要执行的查询语句</param>   
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>
        /// <param name="commandType">执行的SQL语句的类型</param>
        /// <returns></returns> 
        public DbDataReader ExecuteReader(string sql, params object[] parameters)
        {
            DbCommand command = CreateDbCommand(FormatSql(sql, parameters));
            command.Connection.Open();
            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }

        public DbDataReader ExecuteReader(string sql, params DbParameter[] parameters)
        {
            DbCommand command = CreateDbCommand(sql, parameters);
            command.Connection.Open();
            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }

        public DataTable LookupMySqlMeta(string tableName)
        {
            return ExecuteDataTable("show columns from " + tableName);
        }

        public DataTable ExecuteMeta(string tableName)
        {
            using (DbCommand command = CreateDbCommand("select * from " + tableName))
            {
                using (DbDataAdapter adapter = providerFactory.CreateDataAdapter())
                {
                    adapter.SelectCommand = command;
                    DataTable table = new DataTable();
                    adapter.FillSchema(table, SchemaType.Mapped);
                    return table;
                }
            }
        }

        public DataTable ExecuteDataTableWithMeta(string tableName)
        {
            using (DbCommand command = CreateDbCommand("select * from " + tableName))
            {
                using (DbDataAdapter adapter = providerFactory.CreateDataAdapter())
                {
                    adapter.SelectCommand = command;
                    DataTable table = new DataTable();
                    adapter.FillSchema(table, SchemaType.Mapped);
                    adapter.Fill(table);
                    return table;
                }
            }
        }
        /// <summary>   
        /// 执行一个查询语句，返回一个包含查询结果的DataTable   
        /// </summary>   
        /// <param name="sql">要执行的查询语句</param>   
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>
        /// <param name="commandType">执行的SQL语句的类型</param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string sql, params object[] parameters)
        {
            using (DbCommand command = CreateDbCommand(FormatSql(sql, parameters)))
            {
                using (DbDataAdapter adapter = providerFactory.CreateDataAdapter())
                {
                    adapter.SelectCommand = command;
                    DataTable data = new DataTable();
                    adapter.Fill(data);
                    return data;
                }
            }
        }

        /*
        public DataTable ExecuteDataTable(string sql, params DbParameter[] parameters)
        {
            using (DbCommand command = CreateDbCommand(sql, parameters))
            {
                using (DbDataAdapter adapter = providerFactory.CreateDataAdapter())
                {
                    adapter.SelectCommand = command;
                    DataTable data = new DataTable();
                    adapter.Fill(data);
                    return data;
                }
            }
        }
        */

        /// <summary>   
        /// 执行一个查询语句，返回查询结果的第一行第一列   
        /// </summary>   
        /// <param name="sql">要执行的查询语句</param>   
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>   
        /// <param name="commandType">执行的SQL语句的类型</param>
        /// <returns></returns>   
        public Object ExecuteScalar(string sql, params object[] parameters)
        {
            using (DbCommand command = CreateDbCommand(FormatSql(sql, parameters)))
            {
                command.Connection.Open();
                object result = command.ExecuteScalar();
                command.Connection.Close();
                return result;
            }
        }

        /*
        public Object ExecuteScalar(string sql, params DbParameter[] parameters)
        {
            using (DbCommand command = CreateDbCommand(sql, parameters))
            {
                command.Connection.Open();
                object result = command.ExecuteScalar();
                command.Connection.Close();
                return result;
            }
        }
        */

        public DbParameter CreateDbParameter(string name, object value)
        {
            return CreateDbParameter(name, ParameterDirection.Input, value);
        }

        public DbParameter CreateDbParameter(string name, ParameterDirection parameterDirection, object value)
        {
            DbParameter parameter = providerFactory.CreateParameter();
            parameter.ParameterName = name;
            parameter.Value = value;
            parameter.Direction = parameterDirection;
            return parameter;
        }

        /// <summary>
        /// 创建一个DbCommand对象
        /// </summary>
        /// <param name="sql">要执行的查询语句</param>   
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>
        /// <param name="commandType">执行的SQL语句的类型</param>
        /// <returns></returns>
        private DbCommand CreateDbCommand(string sql, params DbParameter[] parameters)
        {
            DbConnection connection = providerFactory.CreateConnection();
            DbCommand command = providerFactory.CreateCommand();
            connection.ConnectionString = ConnectionString;
            command.CommandText = sql;
            command.CommandType = CommandType.Text;
            command.Connection = connection;
            if (!(parameters == null || parameters.Length == 0))
            {
                foreach (DbParameter parameter in parameters)
                {
                    command.Parameters.Add(parameter);
                }
            }
            return command;
        }

        public bool ExecuteNonQueries(List<string> sqls)
        {
            DbConnection connection = providerFactory.CreateConnection();
            connection.ConnectionString = ConnectionString;
            connection.Open();

            DbCommand command = providerFactory.CreateCommand();
            command.Connection = connection;
            command.CommandType = CommandType.Text;
            DbTransaction transaction = connection.BeginTransaction();
            command.Transaction = transaction;
            try
            {
                for (int i = 0; i < sqls.Count; i++)
                {
                    string sql = sqls[i];
                    if (!string.IsNullOrWhiteSpace(sql))
                    {
                        command.CommandText = sql;
                        command.ExecuteNonQuery();
                    }
                }
                transaction.Commit();
                return true;
            }
            catch (Exception e)
            {
                transaction.Rollback();
                throw e;
            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }

        public DataTable getMeta(string tableName)
        {
            string sql = "select object_NAME(c.object_id) as [tableName], c.name as columnName, t.name as dataType, c.max_length as maxLength, sep.value as [description]"
                + " from sys.columns c INNER JOIN sys.types t ON t.system_type_id = c.system_type_id"
                + " left join sys.extended_properties  sep on c.object_id = sep.major_id and sep.name = 'MS_Description'"
                + " where object_NAME(c.object_id) = {0}";
            return ExecuteDataTable(sql,tableName);
        }

        public static string FormatSql(string sql, params object[] args)
        {
            for (int i = 0; i < args.Length; i++)
            {
                args[i] = FormatParam(args[i]);
            }
            return string.Format(sql, args);
        }

        public static string FormatParam(object param)
        {
            if (param == null || param == DBNull.Value)
                return "null";

            if (param is string)
            {
                return '\'' + ((string)param).Replace(@"'", "''") + '\'';
            }

            if (param is DateTime)
            {
                return '\'' + ((DateTime)param).ToString("yyyy-MM-dd HH:mm:ss") + '\'';
            }

            if (param is DateTimeOffset)
            {
                return '\'' + ((DateTimeOffset)param).ToString("yyyy-MM-dd HH:mm:ss zzz") + '\'';
            }

            if (param is char)
            {
                return "'" + param + "'";
            }

            return param.ToString();
        }
    }

    /// <summary>
    /// 数据库类型枚举
    /// </summary>
    public enum DbProviderType : byte
    {
        SqlServer,
        MySql,
        SQLite,
        Oracle,
        ODBC,
        OleDb,
        Firebird,
        PostgreSql,
        DB2,
        Informix,
        SqlServerCe
    }

    /// <summary>
    /// DbProviderFactory工厂类
    /// </summary>
    public class ProviderFactory
    {
        private static Dictionary<DbProviderType, string> providerInvariantNames = new Dictionary<DbProviderType, string>();
        private static Dictionary<DbProviderType, DbProviderFactory> providerFactoies = new Dictionary<DbProviderType, DbProviderFactory>(20);
        static ProviderFactory()
        {
            //加载已知的数据库访问类的程序集
            providerInvariantNames.Add(DbProviderType.SqlServer, "System.Data.SqlClient");
            providerInvariantNames.Add(DbProviderType.OleDb, "System.Data.OleDb");
            providerInvariantNames.Add(DbProviderType.ODBC, "System.Data.ODBC");
            providerInvariantNames.Add(DbProviderType.Oracle, "System.Data.OracleClient");
            providerInvariantNames.Add(DbProviderType.MySql, "MySql.Data.MySqlClient");
            providerInvariantNames.Add(DbProviderType.SQLite, "System.Data.SQLite");
            providerInvariantNames.Add(DbProviderType.Firebird, "FirebirdSql.Data.Firebird");
            providerInvariantNames.Add(DbProviderType.PostgreSql, "Npgsql");
            providerInvariantNames.Add(DbProviderType.DB2, "IBM.Data.DB2.iSeries");
            providerInvariantNames.Add(DbProviderType.Informix, "IBM.Data.Informix");
            providerInvariantNames.Add(DbProviderType.SqlServerCe, "System.Data.SqlServerCe");
        }

        /// <summary>
        /// 获取指定数据库类型对应的程序集名称
        /// </summary>
        /// <param name="providerType">数据库类型枚举</param>
        /// <returns></returns>
        public static string GetProviderInvariantName(DbProviderType providerType)
        {
            return providerInvariantNames[providerType];
        }

        /// <summary>
        /// 获取指定类型的数据库对应的DbProviderFactory
        /// </summary>
        /// <param name="providerType">数据库类型枚举</param>
        /// <returns></returns>
        public static DbProviderFactory GetDbProviderFactory(DbProviderType providerType)
        {
            //如果还没有加载，则加载该DbProviderFactory
            lock (providerFactoies)
            {
                if (!providerFactoies.ContainsKey(providerType))
                {
                    providerFactoies.Add(providerType, ImportDbProviderFactory(providerType));
                }
                return providerFactoies[providerType];
            }
        }

        /// <summary>
        /// 加载指定数据库类型的DbProviderFactory
        /// </summary>
        /// <param name="providerType">数据库类型枚举</param>
        /// <returns></returns>
        private static DbProviderFactory ImportDbProviderFactory(DbProviderType providerType)
        {
            if (!providerInvariantNames.ContainsKey(providerType))
                return null;

            string providerName = providerInvariantNames[providerType];
            return DbProviderFactories.GetFactory(providerName);
        }
    }

    public class DataRowEx
    {
        private DataRow row;
        public DataRowEx(DataRow row)
        {
            this.row = row;
        }

        static public implicit operator DataRowEx(DataRow row)
        {
            return new DataRowEx(row);
        }

        public object this[string columnName]
        {
            get
            {
                object o = row[columnName];
                if (o != DBNull.Value)
                    return o;

                return null;
                /*
                Type type = row.Table.Columns[columnName].DataType;
                if (!type.IsValueType)
                    return null;

                if (type == typeof(DateTime))
                    return DateTime.MinValue;

                if (type == typeof(int))
                    return 0;

                if (type == typeof(float))
                    return 0;

                return o;
                 * */
            }
            set
            {
                row[columnName] = value;
            }
        }
    }
}

